package ch.wsl.postgres2mapperdao.model

/**
 * 
 * Postgres2mapperdao
 * 
 * @author Andrea Minetti
 *
 * 21 Sep 2012
 * 
 */

import com.googlecode.mapperdao.utils._
import com.googlecode.mapperdao._
import ch.wsl.postgres2mapperdao.common.DatabaseConnection


class TableConstraints(
  
    val table_name: String,
    val constraint_type: String,
    val constraint_name: String,
    val constraint_schema: String,
    val refColumns:Set[ConstraintColumnUsage]

) {}



class TableConstraintsDao(val mapperDao: MapperDao, val queryDao: QueryDao) extends SimpleCRUD[TableConstraints, Int] with SimpleAll[TableConstraints] {
	import queryDao._
	import Query._
	
	private val t = TableConstraintsEntity
	private val c = ConstraintColumnUsageEntity
	
	def primaryKeys(schema: String) = query(select from t where t.constraint_type === "PRIMARY KEY" and t.constraint_schema === schema)
	def primaryKeys(schema: String, table_name: String) = query(select from t where t.constraint_type === "PRIMARY KEY" and t.constraint_schema === schema and t.table_name === table_name)
	
	val entity = TableConstraintsEntity
}


object TableConstraintsEntity extends SimpleEntity[TableConstraints]("information_schema.table_constraints", classOf[TableConstraints]) {
	
	DatabaseConnection.register(this)

	val table_name = column("table_name") to (_.table_name)
	val constraint_schema = column("constraint_schema") to (_.constraint_schema)
	val constraint_type = column("constraint_type") to (_.constraint_type)
	val constraint_name = key("constraint_name") to (_.constraint_name)
	val refColumns = onetomany(ConstraintColumnUsageEntity) foreignkey("constraint_name") to (_.refColumns)

	
	def constructor(implicit m:ValuesMap) = new TableConstraints(table_name, constraint_type, constraint_name, constraint_schema,refColumns) with Persisted

}


class ReferentialConstraints(

    val constraint_name: String,
    val unique_constraint_name: String,
    val constraint_schema: String,
    val refColumns: Set[KeyColumnUsage],
    val refExtColumns: Set[ConstraintColumnUsage],
    val refTable: TableConstraints

) {
  
  lazy val table_name_external: String = {
    
    if(refColumns.size == 1 && refColumns.first.column_name.endsWith("_id")) {
      refColumns.first.column_name.substring(0,refColumns.first.column_name.length()-3)
    } else if(refExtColumns.first.table_name.endsWith("_")) {
      refExtColumns.first.table_name + "ref"
    } else {
      refExtColumns.first.table_name
    }
  }
  
  lazy val nonNullable = refColumns.filter(c => c.column_detail.nullable).size == 0
  
}


class ReferentialConstraintsDao(val mapperDao: MapperDao, val queryDao: QueryDao) extends SimpleCRUD[ReferentialConstraints, Int] with SimpleAll[ReferentialConstraints] {
	import queryDao._
	import Query._
	
	private val r = ReferentialConstraintsEntity
	private val c = ConstraintColumnUsageEntity
	private val t = TableConstraintsEntity
	
	def foreignKeys(schema: String) = query(select from r where r.constraint_schema === schema)
	def foreignKeys(schema: String, table_name: String) = query(select from r join(r, r.refTable, t) where r.constraint_schema === schema and t.table_name === table_name)
	
	val entity = ReferentialConstraintsEntity
}


object ReferentialConstraintsEntity extends SimpleEntity[ReferentialConstraints]("information_schema.referential_constraints", classOf[ReferentialConstraints]) {
	
	DatabaseConnection.register(this)
  
	val constraint_name = key("constraint_name") to (_.constraint_name)
	val unique_constraint_name = column("unique_constraint_name") to (_.unique_constraint_name)
	val constraint_schema = column("constraint_schema") to (_.constraint_schema)
	val refColumns = onetomany(KeyColumnUsageEntity) foreignkey("constraint_name") to (_.refColumns)
	val refLocalColumns = onetomany(ConstraintColumnUsageEntity) foreignkey("constraint_name") to (_.refExtColumns)
	val refTable = manytoone(TableConstraintsEntity) foreignkey("constraint_name") to (_.refTable)
	
	def constructor(implicit m:ValuesMap) = new ReferentialConstraints(constraint_name,unique_constraint_name, constraint_schema, refColumns, refLocalColumns, refTable) with Persisted

}

class ConstraintColumnUsage(

    val constraint_name: String,
    val column_name: String,
    val table_name:String

) {
  
}


class ConstraintColumnUsageDao(val mapperDao: MapperDao, val queryDao: QueryDao) extends SimpleCRUD[ConstraintColumnUsage, Int] with SimpleAll[ConstraintColumnUsage] {
	import queryDao._
		
	val entity = ConstraintColumnUsageEntity
}


object ConstraintColumnUsageEntity extends SimpleEntity[ConstraintColumnUsage]("information_schema.constraint_column_usage", classOf[ConstraintColumnUsage]) {
	
	DatabaseConnection.register(this)
  
	val constraint_name = column("constraint_name") to (_.constraint_name)
	val column_name = key("column_name") to (_.column_name)
	val table_name = column("table_name") to (_.table_name)

	
	def constructor(implicit m:ValuesMap) = new ConstraintColumnUsage(constraint_name,column_name, table_name) with Persisted

}

class KeyColumnUsage(

    val constraint_name: String,
    val column_name: String,
    val table_name:String,
    val column_detail: Column

) {}


class KeyColumnUsageDao(val mapperDao: MapperDao, val queryDao: QueryDao) extends SimpleCRUD[KeyColumnUsage, Int] with SimpleAll[KeyColumnUsage] {
	import queryDao._
		
	val entity = KeyColumnUsageEntity
}


object KeyColumnUsageEntity extends SimpleEntity[KeyColumnUsage]("information_schema.key_column_usage", classOf[KeyColumnUsage]) {
	
	DatabaseConnection.register(this)
  
	val constraint_name = column("constraint_name") to (_.constraint_name)
	val column_name = key("column_name") to (_.column_name)
	val table_name = column("table_name") to (_.table_name)
	val column_detail = manytoone(ColumnEntity) foreignkeys(List("column_name","table_name")) to(_.column_detail)

	
	def constructor(implicit m:ValuesMap) = new KeyColumnUsage(constraint_name,column_name, table_name, column_detail) with Persisted

}

/**
 *
 * GET ALL PRIMARY KEYS
 * 
 * SELECT t.table_name,t.constraint_type,c.column_name,t.constraint_name
 * FROM information_schema.table_constraints AS t 
 * JOIN information_schema.constraint_column_usage AS c ON t.constraint_name = c.constraint_name 
 * WHERE t.constraint_type = 'PRIMARY KEY'
 * ORDER BY t.table_name
 * 
 * 
 * GET ALL FOREIGN KEYS
 * 
 * SELECT t.table_name,t2.table_name,c.column_name
 * FROM information_schema.referential_constraints AS r
 * JOIN information_schema.table_constraints AS t ON r.constraint_name = t.constraint_name
 * JOIN information_schema.table_constraints AS t2 ON r.unique_constraint_name = t2.constraint_name
 * JOIN information_schema.constraint_column_usage AS c ON r.unique_constraint_name = c.constraint_name
 * 
 */